#coding:utf-8
import pandas as pd
import csv
import numpy as np
from datetime import datetime
#读取文件

dou1=pd.read_excel(r'G:\工作\每日\抖音1.xlsx',dtype={'主订单编号':str,'子订单编号':str,'商品ID':str,'达人昵称':str,'选购商品':str,'达人ID':str})
dou1['平台']='抖音1'

dou2=pd.read_excel(r'G:\工作\每日\抖音2.xlsx',dtype={'主订单编号':str,'子订单编号':str,'商品ID':str,'达人昵称':str,'选购商品':str,'达人ID':str})
dou2['平台']='抖音2'

dou3=pd.read_excel(r'G:\工作\每日\抖音3.xlsx',dtype={'主订单编号':str,'子订单编号':str,'商品ID':str,'达人昵称':str,'选购商品':str,'达人ID':str})
dou3['平台']='抖音3'

dou4=pd.read_excel(r'G:\工作\每日\抖音4.xlsx',dtype={'主订单编号':str,'子订单编号':str,'商品ID':str,'达人昵称':str,'选购商品':str,'达人ID':str})
dou4['平台']='抖音4'

dou=pd.concat([dou1,dou2,dou3,dou4])


ld=pd.read_excel(r'G:\工作\每日\刘栋达人.xlsx',dtype={'抖音号名称':str})

lxx1=pd.read_excel(r'G:\工作\每日\罗欣欣达人.xlsx',dtype={'抖音号名称':str}) #(桶装)（袋装）
lxx2=pd.read_excel(r'G:\工作\每日\罗欣欣土豆粉达人.xlsx',dtype={'抖音号名称':str}) #(土豆粉)
xhwh1=pd.read_excel(r'G:\工作\每日\夕湖文化.xlsx',dtype={'抖音号名称':str}) 
hn1=pd.read_excel(r'G:\工作\每日\花妞.xlsx',dtype={'抖音号名称':str}) #(土豆粉)
tt1=pd.read_excel(r'G:\工作\每日\团团.xlsx',dtype={'抖音号名称':str}) #(土豆粉)
z1=pd.read_excel(r'G:\工作\每日\招商1.xlsx',dtype={'订单id':str})
z2=pd.read_excel(r'G:\工作\每日\招商2.xlsx',dtype={'订单id':str})
z3=pd.read_excel(r'G:\工作\每日\招商3.xlsx',dtype={'订单id':str})
z4=pd.read_excel(r'G:\工作\每日\招商4.xlsx',dtype={'订单id':str})

y1=pd.read_excel(r'G:\工作\每日\佣金1.xlsx',dtype={'订单id':str})
y2=pd.read_excel(r'G:\工作\每日\佣金2.xlsx',dtype={'订单id':str})
y3=pd.read_excel(r'G:\工作\每日\佣金3.xlsx',dtype={'订单id':str})
y4=pd.read_excel(r'G:\工作\每日\佣金4.xlsx',dtype={'订单id':str})
#y3=pd.read_excel(r'G:\工作\每日\佣金3.xlsx')
#dou=dou[(dou['订单应付金额'] >= 0.01)]
dou=dou[(dou['商家编码'] !='抖音直播间赠品')]

#筛选状态
dou=dou[(( dou['订单状态']=="已签收")  | ( dou['订单状态']=="待发货" ) | ( dou['订单状态']=="已完成")  | ( dou['订单状态']=="已发货")  | ( dou['订单状态']=="已支付" ) | ( dou['订单状态']=="部分发货" ))
        &( dou['售后状态'] != "退款成功") & (dou['售后状态'] != "售后完成")  & (dou['售后状态'] != "退款完成")  & (dou['售后状态'] != "已全额退款" )]

dou_date=dou.loc[:,['子订单编号','商品ID','选购商品','商品规格','商家编码','商品数量','订单应付金额','订单状态','售后状态','流量来源','达人昵称','达人ID','广告渠道','流量类型','流量渠道','流量体裁','订单提交时间','平台实际承担优惠金额','达人实际承担优惠金额','平台','是否福袋订单']]

dou_date.columns=['订单id','商品ID','选购商品','商品规格','商家编码','商品数量','订单应付金额','订单状态','售后状态','流量来源','达人昵称','达人ID','广告渠道','流量类型','流量渠道','流量体裁','订单提交时间','平台实际承担优惠金额','达人实际承担优惠金额','平台','福袋']
#去空格
dou_date['商品ID']=dou_date['商品ID'].str.strip()
dou_date['商家编码']=dou_date['商家编码'].str.strip()
dou_date['订单id']=dou_date['订单id'].str.strip()
dou_date['达人ID']=dou_date['达人ID'].str.strip()
dou_date['福袋'].fillna('否',inplace=True)
def g(x,y): #x:选购商品 y:商品规格
    if '电煮锅' in x or '电煮锅' in y or'拉面锅' in x or '拉面锅' in y:
        return '电煮锅赠品'
    else :
        return '无'
dou_date['电煮锅']=list(map(g,list(dou_date['选购商品']),list(dou_date['商品规格'])))

#佣金表
y=pd.concat([y1,y2,y3,y4])
y_date=y.loc[:,['订单id','作者账号','预估佣金支出']] 
y_date['订单id']=y_date['订单id'].str.strip()
dou_date1=pd.merge(dou_date,y_date,on='订单id',how='left')
dou_date1['达人昵称'].fillna(dou_date1['作者账号'],inplace=True)

#招商表
z1_date=z1.loc[:,['订单id','出单机构','预估服务费收入']]   
z2_date=z2.loc[:,['订单id','出单机构','预估服务费收入']]
z3_date=z3.loc[:,['订单id','出单机构','预估服务费收入']]   
z4_date=z4.loc[:,['订单id','出单机构','预估服务费收入']]
zs=pd.concat([z1_date,z2_date,z3_date,z4_date])

dou_date2=pd.merge(dou_date1,zs,on='订单id',how='left')

#各投流链接,达人

lc_lj=['3752066905167495351','3754479940293230816']   #良辰链接
lc_dr=[]   #良辰达人

ld_lj=['3524421208374397998','3718871706278953300','3764710268731523333']   #刘栋单独链接

ld_dr=list(ld['抖音号名称'])

lxx1_lj=['3702367630972289253','3753012046921335096','3701090938974765405','3506957255406950391','3520740088860524483','3745946018349449594','3758543932670214558','3758958156538052831','3775109632327024899']#(罗欣欣达人）
lxx1_dr=list(lxx1['抖音号名称'])  #罗欣欣达人

lxx2_lj=['3748189374425203566','3751738608588030256','3752292420973363354','3737638082678227388','3628547567660489014','3694732441748111734','3754689465759694891','3712805099375034797','3674502993446830541','3766406205405266422']#(土豆粉达人)
lxx2_dr=list(lxx2['抖音号名称']) #罗欣欣土豆粉达人 
xhwh1_lj=['3751738608588030256','3687141961262825560','3758958156538052831','3723151364247912548'] #夕湖文化
xhwh1_dr=list(xhwh1['抖音号名称']) 
hn1_lj=['3701090938974765405','3758958156538052831','3687141961262825560','3775109632327024899'] #花妞千川——爆品侠
hn1_dr=list(hn1['抖音号名称']) 

tt1_lj=['3751738608588030256','3758958156538052831','3687141961262825560','3737638082678227388','3628547567660489014','3674502993446830541','3694732441748111734'] #团团别吃了
tt1_dr=list(tt1['抖音号名称']) 
def f(x,y,z,v,s,w,p):  #x: 链接 #y: 达人昵称 #z:选购商品 #v:广告渠道 #s:流量来源 #w:出单机构
    if  w in['武汉海大电子商务有限公司'] :   #罗欣欣运营账号
        return '嗨大'
    
    elif  w in['卓敏科技','紫金优选','瀚睿科技','红利传媒','来吉团队','金莎传媒'] :   #罗欣欣运营账号
        return '冷漠'
    
    
    elif (x in ld_lj ) and ( (y in ld_dr )  or (pd.isnull(y))):  #去掉招商达人，加上自然单
        return '刘栋单独链接'
    elif (x  in ['3751738608588030256'] ) and (y in ld_dr ) : 
        return '刘栋'
    
    elif  y in['harry5'] : 
        return 'harry5'
    
    elif  y in['喜喜小助理（直播返场）'] : 
        return '喜喜投流'
    elif  y in['张喜喜'] : 
        return '喜喜投流大号'

    elif  y in['美食挖掘机🍜','饱饱吃不饱','金金会发光','味蕾探险家','饭桶爱说笑'] :   #罗欣欣运营账号
        return '罗欣欣账号'
    
    elif  y in['啦啦啦lalala','怪咖美食记','爱吃的光远','空壳少年','食光里的馋猫🐱','宛禾食品甄选号','大馋丫头可可'] : #白鹏飞运营账号
        return '白鹏飞账号'
    elif (x in xhwh1_lj ) and (y in xhwh1_dr ) : 
        return '夕湖文化'
    
    elif (x in tt1_lj ) and (y in tt1_dr ) :
        return '罗欣欣'

    elif (x in lxx1_lj ) and (y in lxx1_dr ) : 
        return '罗欣欣'
    
    elif (x in lxx2_lj ) and (y in lxx2_dr ) : 
        return '罗欣欣'
    elif (x in hn1_lj ) and (y in hn1_dr ) : 
        return '代投花妞'
    
    elif (x  in ['3751738608588030256'] ) and (y in hn1_dr ) : 
        return '花妞'
    elif (x in lc_lj ) :
        return '莨辰'
    elif (x  in ['3768784577812889610'] )  : 
        return '木森'
    elif  x  in ['3735010733180715297','3741925082977665083']  : #罗欣欣单独链接
        return '罗欣欣账号'
    elif  x  in ['3723120728170692876','3723119143235485902','3741926701987398242']  : #白鹏飞单独链接
        return '白鹏飞账号'
      
    elif  x  in ['3714480675638870403','3741923461510070306','3550782895822129982','3526108849595662124','3699396698997653933']  : 
        return '共用链接自然单'
    
    #elif (v =='商品卡')and (x  in ['3712813994243915957','3735010733180715297','3713734480830267578','3712805099375034797','3712785316638228820','3738929837008617904','3746121270362767689','3741926278975062262','3714568370264342992','3743556622891483314','3719404361806381548','3741928376991088750','3738931359733907468','3668404150531653930','3686235918408221166','3699396698997653933','3686084716408209458']):
        #return '罗欣欣3店商品卡'
    
    #elif  (x  in ['3712813994243915957','3735010733180715297','3713734480830267578','3712805099375034797','3712785316638228820','3738929837008617904','3746121270362767689','3741926278975062262','3714568370264342992','3743556622891483314','3719404361806381548','3741928376991088750','3738931359733907468','3668404150531653930','3686235918408221166','3699396698997653933','3686084716408209458']):
        #return '罗欣欣账号'

    elif (v =='商品卡')and (('lcc' in z)  or ('lxx' in z)):
        return '罗欣欣3店商品卡'
    
    elif (('lcc' in z)  or ('lxx' in z)):
        return '罗欣欣账号'
   
    elif y=='宛禾米线官方旗舰店' or ('z1' in z)  or (' Z1' in z):
        return '自播1'
    elif y=='宛禾米线速食官方旗舰店'  or y=='宛禾米线' or y=='宛禾速食官方旗舰店' or y=='宛禾速食直播间' or ('z3' in z)  or (' Z3' in z):
        return '自播3'
    elif y=='宛禾米线速食旗舰店' or ('lsf' in z) :
        return '螺蛳土豆粉直播间'
    elif y=='宛禾食品旗舰店' or ('z5' in z)  or ('Z5' in z) :
        return '自播5'
    elif y=='宛禾速食旗舰店' or y=='宛禾食品旗舰店甄选号' or ('z6' in z)  or ('Z6' in z):
        return '自播6'
    elif y=='宛禾官方旗舰店' or ('z8' in z)  or (' Z8' in z) :
        return '自播8'
    elif p=='抖音1' and ('dsp' in z) :
        return '自播1'
    elif p=='抖音2' and ('dsp' in z) :
        return '自播5'
    elif p=='抖音3' and ('dsp' in z) :
        return '自播8'
    elif p=='抖音4' and ('dsp' in z) :
        return '螺蛳土豆粉直播间'
    
    elif   (v =='商品卡'): #投流商品卡
        return '超级商品卡'
    
    elif pd.notnull(y):
        return '达人分发'
    elif s=='精选联盟':
        return '达人分发'
     
    else:
        return '自然单'
        


dou_date2['投流账户'] =list(map(f,list(dou_date2['商品ID']),list(dou_date2['达人昵称']),list(dou_date2['选购商品']),list(dou_date2['广告渠道']),list(dou_date2['流量来源']),list(dou_date2['出单机构']),list(dou_date2['平台'])))

def m(x,y,z,v,s,w):  #x: 链接 #y: 达人昵称 #z:选购商品 #v:广告渠道 #s:流量来源 #w:出单机构
    if  w in['武汉海大电子商务有限公司'] :
        return '外部团队达人分发'
    
    elif  w in['卓敏科技','紫金优选','瀚睿科技','红利传媒','来吉团队','金莎传媒'] : 
        return '外部团队达人分发'
    
    
    
    elif (x in ld_lj ) and ( (y in ld_dr )  or (pd.isnull(y))): 
        return '内部短视频投流'
    elif (x  in ['3751738608588030256'] ) and (y in ld_dr ) : 
        return '内部短视频投流'
    
    elif  y in['harry5'] : 

        return '内部达人分发'
    
    elif  y in['喜喜小助理（直播返场）'] : 
        return '内部达人分发'
    elif  y in['张喜喜'] : 
        return '内部达人分发'
    elif  y in['美食挖掘机🍜','饱饱吃不饱','金金会发光','味蕾探险家','饭桶爱说笑'] :   #罗欣欣运营账号
        return '内部短视频投流'
    
    elif  y in['啦啦啦lalala','怪咖美食记','爱吃的光远','空壳少年','食光里的馋猫🐱','宛禾食品甄选号','大馋丫头可可'] : #白鹏飞运营账号
        return '内部短视频投流'
    
    elif (x in xhwh1_lj ) and (y in xhwh1_dr ) : 
        return '外部短视频投流'
    
    elif (x in tt1_lj ) and (y in tt1_dr ) : 
        return '内部达人分发'

    elif (x in lxx1_lj ) and (y in lxx1_dr ) : 
        return '内部达人分发'
    
    elif (x in lxx2_lj ) and (y in lxx2_dr ) : 
        return '内部达人分发'
    elif (x in hn1_lj ) and (y in hn1_dr ) : 
        return '外部短视频投流'
    elif (x  in ['3751738608588030256'] ) and (y in hn1_dr ) : 
        return '外部短视频投流'
    elif (x in lc_lj ) :
        return '外部短视频投流'
    
    elif  x  in ['3735010733180715297','3741925082977665083']  : #罗欣欣单独链接
        return '内部短视频投流'
    elif  x  in ['3723120728170692876','3723119143235485902','3741926701987398242']  : #白鹏飞单独链接
        return '内部短视频投流'
    
    elif  x  in ['3714480675638870403','3741923461510070306','3550782895822129982','3526108849595662124','3699396698997653933']  : 
        return '内部短视频投流'
    
    
    #elif (v =='商品卡')and (x  in ['3712813994243915957','3735010733180715297','3713734480830267578','3712805099375034797','3712785316638228820','3738929837008617904','3746121270362767689','3741926278975062262','3714568370264342992','3743556622891483314','3719404361806381548','3741928376991088750','3738931359733907468','3668404150531653930','3686235918408221166','3699396698997653933','3686084716408209458']):
        #return '超级商品卡'
   
    elif (v =='商品卡')and (('lcc' in z)  or ('lxx' in z)):
        return '超级商品卡'
    
    
    elif  ('lcc' in z)  or ('lxx' in z):
        return '内部短视频投流'
    
    elif (x  in ['3768784577812889610'] )  : 
        return '内部达人分发'
    
    
    
    elif y=='宛禾米线官方旗舰店' or ('z1' in z)  or ('Z1' in z):
        return '自播'
    elif y=='宛禾米线速食官方旗舰店'  or y=='宛禾米线' or y=='宛禾速食官方旗舰店' or y=='宛禾速食直播间' or y=='宛禾速食' or ('z3' in z)  or ('Z3' in z):
        return '自播'
    elif y=='宛禾米线速食旗舰店' or ('lsf' in z) :
        return '自播'
    elif y=='宛禾食品旗舰店' or ('z5' in z)  or ('Z5' in z)or ('dsp' in z):
        return '自播'
    elif y=='宛禾速食旗舰店' or y=='宛禾食品旗舰店甄选号' or ('z6' in z)  or ('Z6' in z):
        return '自播'
    elif y=='宛禾官方旗舰店' or ('z8' in z)  or ('Z8' in z) :
        return '自播'
    
    elif   (v =='商品卡'): #投流商品卡
        return '超级商品卡'
    
    
    elif pd.notnull(y):
        return '内部达人分发'
    elif s=='精选联盟':
        return '内部达人分发'
    else:
        return '自然单'
    
dou_date2['总渠道'] =list(map(m,list(dou_date2['商品ID']),list(dou_date2['达人昵称']),list(dou_date2['选购商品']),list(dou_date2['广告渠道']),list(dou_date2['流量来源']),list(dou_date2['出单机构'])))    
#a=dou_date2[dou_date2['平台']=='抖音4']
#a.to_excel(r'G:\结果\周度利润测算明细_抖音2.xlsx')
dou_=dou_date2.groupby([dou_date2['平台'],dou_date2['总渠道'],dou_date2['投流账户'],dou_date2['商品ID'],dou_date2['商家编码'],dou_date2['福袋'],dou_date2['电煮锅']],as_index=False).agg(订单实付=('订单应付金额','sum'),商品数量=('商品数量','sum'),平台优惠=('平台实际承担优惠金额','sum'),达人优惠=('达人实际承担优惠金额','sum'),佣金=('预估佣金支出','sum'),招商佣金=('预估服务费收入','sum'))
dou_['成交金额']=dou_['订单实付']+dou_['平台优惠']+dou_['达人优惠']

dou_.to_excel(r'G:\结果\周度利润测算明细_抖音1.xlsx')